const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
    host: 'localhost',
    port: 3306,
    user: 'tongfengqi',
    password: 'tongfengqi',
    database: 'tongfengqi',
    charset: 'utf8mb4'
};

async function reindexJobPostings() {
    console.log('开始重新编排岗位发布ID...');
    
    let connection = null;
    try {
        // 连接数据库
        connection = await mysql.createConnection(dbConfig);
        console.log('已连接到数据库');
        
        // 开启事务
        await connection.query('START TRANSACTION');
        
        // 重新编排ID为降序（确保连续）
        console.log('执行ID重新编排(降序)...');
        // 先禁用主键约束检查（如果需要）
        await connection.query('SET FOREIGN_KEY_CHECKS = 0');
        // 先将所有ID设置为负数，避免冲突
        await connection.query('UPDATE job_postings SET id = -id');
        // 然后按创建时间降序重新分配连续ID
        await connection.query('SET @rank = 0');
        await connection.query('UPDATE job_postings SET id = (@rank := @rank + 1) ORDER BY created_at DESC');
        // 重新启用主键约束检查
        await connection.query('SET FOREIGN_KEY_CHECKS = 1');
        // 检查所有记录确保ID连续
        const [checkResult] = await connection.query('SELECT COUNT(*) as total, MIN(id) as min_id, MAX(id) as max_id FROM job_postings');
        console.log(`记录检查: 总数=${checkResult[0].total}, 最小ID=${checkResult[0].min_id}, 最大ID=${checkResult[0].max_id}`);
        
        // 重置自增计数器
        const [maxIdResult] = await connection.query('SELECT COALESCE(MAX(id), 0) + 1 as nextId FROM job_postings');
        const nextId = maxIdResult[0].nextId;
        await connection.query(`ALTER TABLE job_postings AUTO_INCREMENT = ${nextId}`);
        console.log(`已重置自增计数器为: ${nextId}`);
        
        // 提交事务
        await connection.query('COMMIT');
        
        // 查询重新编排后的结果，按ID排序显示所有记录
        const [results] = await connection.query('SELECT id, title, created_at FROM job_postings ORDER BY id ASC');
        
        console.log('\nID重新编排完成！结果如下：');
        console.log('----------------------------------');
        console.log('ID | 标题 | 创建时间');
        console.log('----------------------------------');
        results.forEach(job => {
            console.log(`${job.id} | ${job.title} | ${new Date(job.created_at).toString()}`);
        });
        console.log('----------------------------------');
        console.log(`共重新编排了 ${results.length} 条记录`);
        // 验证ID连续性
        console.log('\nID连续性验证：');
        for(let i = 1; i <= results.length; i++) {
            const found = results.find(job => job.id === i);
            if (found) {
                console.log(`ID ${i}: 存在 (${found.title})`);
            } else {
                console.log(`ID ${i}: 缺失！`);
            }
        }
        
    } catch (error) {
        console.error('重新编排ID时出错:', error);
        // 如果出错，回滚事务
        if (connection) {
            try {
                await connection.execute('ROLLBACK');
                console.log('事务已回滚');
            } catch (rollbackError) {
                console.error('回滚事务时出错:', rollbackError);
            }
        }
    } finally {
        // 关闭数据库连接
        if (connection) {
            await connection.end();
            console.log('数据库连接已关闭');
        }
    }
}

// 执行重新编排
reindexJobPostings().catch(console.error);